package org.posupe.cursos;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.posupe.bdr.PersistenceMechanismException;
import org.posupe.bdr.PersistenceMechanismRDBMS;
import org.posupe.util.RepositorioException;

public class RepositorioCursosBDR implements RepositorioCursos {
	
	    private static RepositorioCursosBDR instance;
	    private PersistenceMechanismRDBMS pm;
		
	   private RepositorioCursosBDR() throws RepositorioException {
			try {
	            pm = PersistenceMechanismRDBMS.getInstance();
	            pm.connect();
	        }
	        catch (Exception ex) {
	           throw new RepositorioException(ex);
	        }
	    }

	    public synchronized static RepositorioCursosBDR getInstance() throws RepositorioException {
	        if (instance == null) {
	            	instance = new RepositorioCursosBDR();
				
	        }
	        return instance;
	    }

	    public void inserir(Curso curso) throws RepositorioException {
	        try {
	        	    String sql = "INSERT INTO CURSO(CODIGO, NOME, DESCRICAO, NIVEL, COD_INSTITUICAO, CARGAHORARIA) VALUES (?,?,?,?,?,?)";
        			PreparedStatement statement = (PreparedStatement) pm.getCommunicationChannel(sql);
        			statement.setInt(1, curso.getCodigo());
        			statement.setString(2, curso.getNome());
        			statement.setString(3, curso.getDescricao());
        			statement.setString(4, curso.getNivel());
        			statement.setString(5, curso.getInstituicaoEnsino().getCodigo());
        			statement.setInt(6, curso.getCargaHoraria());
        			statement.executeUpdate();
	            
	        } catch(PersistenceMechanismException e){
	            throw new RepositorioException(e);
	        } catch (SQLException e) {
	            throw new RepositorioException(e);
		    } finally {
			    try {
					pm.releaseCommunicationChannel();
				} catch (PersistenceMechanismException ex) {
					throw new RepositorioException(ex);
				}
	        }
	    }

		public void remover(int codigo) throws CursoInexistenteException, RepositorioException {
	        try{
	        	String sql = "DELETE FROM CURSO WHERE CODIGO = ? ";
	        	PreparedStatement statement = (PreparedStatement) pm.getCommunicationChannel(sql);
	        	statement.setInt(1, codigo);
	            int i = statement.executeUpdate();
	            if (i == 0) {
	            	throw new CursoInexistenteException(codigo);
	            }
			} catch(PersistenceMechanismException e){
	            throw new RepositorioException(e);
			} catch(SQLException e){
	            throw new RepositorioException(e);            
			} finally {
				try {
					pm.releaseCommunicationChannel();
				} catch (PersistenceMechanismException ex) {
					throw new RepositorioException(ex);
				}
			}
		}

		public Curso procurar(int codigo) throws CursoInexistenteException, RepositorioException {
			Curso curso = null;
	        try {
	        	String sql = "SELECT CODIGO, NOME, DESCRICAO, NIVEL, CARGAHORARIA, COD_INSTITUICAO FROM CURSO WHERE CODIGO = ? ";
	        	PreparedStatement statement = (PreparedStatement) pm.getCommunicationChannel(sql);
	        	statement.setInt(1, codigo);
	            ResultSet resultset = statement.executeQuery();
	            if (resultset.next()) {
	                curso = new Curso(resultset.getInt("CODIGO"),
	                				  resultset.getString("NOME"),
	                				  resultset.getString("DESCRICAO"),
	                		          resultset.getString("NIVEL"),
	                		          null, //TODO RepositorioInstituicaoEnsino.getInstance().procurar(resultset.getString("COD_INSTITUICAO");
	                		          resultset.getInt("CARGAHORARIA"));
	            } else {
	            	throw new CursoInexistenteException(codigo);
	            }
				resultset.close();
			} catch(PersistenceMechanismException e){
	            throw new RepositorioException(e);
	        } catch (SQLException e) {
				throw new RepositorioException(e);
			} finally {
				try {
					pm.releaseCommunicationChannel();
				} catch (PersistenceMechanismException ex) {
					throw new RepositorioException(ex);
				}
			}
			return curso;
		}

		public void atualizar(Curso curso) throws CursoInexistenteException, RepositorioException {
		    try {
		    	String sql = "UPDATE CURSO SET NOME = ?, DESCRICAO = ?, NIVEL = ?, COD_INSTITUICAO = ?, CARGA_HORARIA = ? " + 
		                     "WHERE CODIGO = ? ";
	    	    PreparedStatement statement =  (PreparedStatement)pm.getCommunicationChannel(sql);
	    	    statement.setString(1, curso.getNome());
	    	    statement.setString(2, curso.getDescricao());
	    	    statement.setString(3, curso.getNivel());
	    	    statement.setString(4, curso.getInstituicaoEnsino().getCodigo());
	    	    statement.setInt(5, curso.getCargaHoraria());
	    	    statement.setInt(6, curso.getCodigo());
	    	    int i = statement.executeUpdate();
	            if (i == 0) {
	            	throw new CursoInexistenteException(curso.getCodigo());
	            }
			} catch(PersistenceMechanismException e){
	            throw new RepositorioException(e);
			} catch (SQLException e) {
			    throw new RepositorioException(e);
		    } finally {
		    	try {
					pm.releaseCommunicationChannel();
				} catch (PersistenceMechanismException ex) {
					throw new RepositorioException(ex);
				}
			}
		}

		public boolean existe(int codigo) throws RepositorioException {
	        boolean resposta = false;
	        try {

	        	String sql = "SELECT 1 FROM CURSO WHERE CODIGO = ? ";
	        	PreparedStatement statement = (PreparedStatement) pm.getCommunicationChannel(sql);
	        	statement.setInt(1, codigo);
	            ResultSet resultset = statement.executeQuery();
	            resposta = resultset.next();
				resultset.close();
			} catch(PersistenceMechanismException e){
	            throw new RepositorioException(e);
	        } catch (SQLException e) {
				throw new RepositorioException(e);
			} finally {
				try {
					pm.releaseCommunicationChannel();
				} catch (PersistenceMechanismException ex) {
					throw new RepositorioException(ex);
				}
			}
	        return resposta;
	    }

	    
	    public IteratorCursos getIterator() throws RepositorioException {
        	IteratorCursosVector resultado = new IteratorCursosVector();
	        try {
	        	String sql = "SELECT CODIGO, NOME, DESCRICAO, NIVEL, CARGAHORARIA, COD_INSTITUICAO FROM CURSO";
	            Statement statement = (Statement) pm.getCommunicationChannel();
	            ResultSet resultset = statement.executeQuery(sql);
	            Curso curso = null;
	            while (resultset.next()) {
	            	curso = new Curso(resultset.getInt("CODIGO"),
	            						resultset.getString("NOME"),
	            						resultset.getString("DESCRICAO"),
	            						resultset.getString("NIVEL"),
	            						null, //TODO RepositorioInstituicaoEnsino.getInstance().procurar(resultset.getString("COD_INSTITUICAO");
	            						resultset.getInt("CARGAHORARIA"));
	            	      resultado.add(curso);
	            }
	            resultset.close();
			} catch(PersistenceMechanismException e){
	            throw new RepositorioException(e);
	        } catch (SQLException e) {
				throw new RepositorioException(e);
			} finally {
				try {
					pm.releaseCommunicationChannel();
				} catch (PersistenceMechanismException ex) {
					throw new RepositorioException(ex);
				}
			}
	        return resultado;
	    }

		public IteratorCursos procurar(Curso curso) throws RepositorioException {
			IteratorCursos cursos = this.getIterator();
			IteratorCursosVector resultado = null;
			resultado = new IteratorCursosVector();
				while(cursos.hasNext()){
					Curso cursoAtual = cursos.next();
					if (cursoAtual.match(curso)){
						resultado.add(cursoAtual);
					}
				}
			return resultado;
		}
}